package com.fsnbigdata.repository;
import com.fsnbigdata.domain.Organism;
import com.fsnbigdata.domain.Specimen;
import com.fsnbigdata.domain.dto.NamingPair;
import com.fsnbigdata.domain.dto.NamingPair3;
import java.util.List;

import org.springframework.data.jpa.domain.Specification;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.querydsl.QueryDslPredicateExecutor;
import org.springframework.roo.addon.layers.repository.jpa.annotations.RooJpaRepository;
import org.springframework.roo.addon.layers.repository.jpa.annotations.finder.RooFinder;
import io.springlets.data.jpa.repository.DetachableJpaRepository;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.transaction.annotation.Transactional;

/**
 * = OrganismRepository
 *
 * TODO Auto-generated class documentation
 *
 */
@RooJpaRepository(entity = Organism.class, finders = { @RooFinder(value = "findNameByNameLike", returnType = Organism.class) })
@Transactional(readOnly = true)
public interface OrganismRepository extends DetachableJpaRepository<Organism, Long>, OrganismRepositoryCustom {

    /**
     * TODO Auto-generated method documentation
     *
     * @param name
     * @param pageable
     * @return Page
     */
    public abstract Page<Organism> findAllByNameLike(String name, Pageable pageable);

    /**
     * TODO Auto-generated method documentation
     *
     * @param name
     * @return Long
     */
    public abstract long countByNameLike(String name);

    List<NamingPair3> findAllByInitial(String initial);

    long countByCreator(String creator);
    @Query(value = "select id,name from organism where id=?1",nativeQuery = true)
    public abstract Object queryById(Long s);

//    @Query(value = "select o from organism where id in (select organism_id from taxonomy where family = ?1 and genus = ?2) and name<>''",nativeQuery = true)
//    Organism findByIdAndFamilyAndGenus(Long id,String family,String genus);


    Page<Organism> findAll(Specification<Organism> spec, Pageable pageable);

    @Query(value = "select id,name,latin_name from Organism where id in(?1) ORDER BY ?#{#pageable}",
    countQuery = "select count(*) from Organism where id in(?1)",nativeQuery = true)
    List<Object> findByIds(List<Long> ids, Pageable pageable);

    /**
     * 获取物种的名字，英文名，发现人和发现时间
     * @param ids 物种id集合
     * @return
     */
    @Query(value = "select name,latin_name,named_info from organism where id in(?1)",nativeQuery = true)
    List<Object> getOrganismByIds(List<Long> ids);

    /**
     * 查询特有种列表
     * @param endemism
     * @param ids
     * @return
     */
    @Query(value = "select name,latin_name,named_info from organism where endemism like concat('%',?1,'%') and id in(?2)",nativeQuery = true)
    public List<Object> getOrganismByEndemismAndIds(String endemism,List<Long> ids);
    
    /**
     * 通过特有分布查询
     * @param endemism
     * @return
     */
    @Query(value = "select o from Organism o where endemism like concat('%',?1,'%') and id in(?2)")
    List<Organism> findAllByEndemism(String endemism,List<Long> ids);

    /**
     * 查询特有种列表
     * @param habitat  类型（洞穴、陆生、森林等）
     * @param peculiar  特有种类型
     * @param ids  通过地区查询的物种id集合
     * @return
     */
    @Query(value = "SELECT o.name,o.latin_name,o.named_info FROM organism o WHERE o.habitat LIKE CONCAT('%',?1,'%') AND o.endemism LIKE CONCAT('%',?2,'%') AND id in(?3)",nativeQuery = true)
    public List<Object> findByHabitatAndAreaAndPeculiar(String habitat,String peculiar,List<Long> ids);

    @Query(value = "SELECT * FROM (\n" +
            "(SELECT id,name,latin_name as name_en,'Organism' AS sign  FROM organism WHERE name like concat('%',?1,'%') ORDER BY id) \n" +
            "UNION ALL\n" +
            "(SELECT id,name,latin_name as name_en,'Specimen' AS sign FROM specimen WHERE name like concat('%',?1,'%') ORDER BY id) \n" +
            "UNION ALL\n" +
            "(SELECT id,name,null as name_en,'Park' AS sign FROM park WHERE name like concat('%',?1,'%') ORDER BY id) \n" +
            "UNION ALL\n" +
            "(SELECT id,name,null as name_en,'Ecosystem' AS sign FROM ecosystem WHERE name like concat('%',?1,'%') ORDER BY id) \n" +
            "UNION ALL\n" +
            "(SELECT id,name,latin as name_en,'Conservation_0' AS sign FROM animal_conservation WHERE name like concat('%',?1,'%') ORDER BY id) \n" +
            "UNION ALL\n" +
            "(SELECT id,name,latin as name_en,'Conservation_1' AS sign FROM plant_conservation WHERE name like concat('%',?1,'%') ORDER BY id) \n" +
            "UNION ALL\n" +
            "(SELECT id,name,latin as name_en,'Conservation_2' AS sign FROM endangered_species WHERE name like concat('%',?1,'%') ORDER BY id) \n" +
            "UNION ALL\n" +
            "(SELECT id,name,NULL as name_en,'Vegetation' AS sign FROM vegetation WHERE name like concat('%',?1,'%') ORDER BY id) \n" +
            "UNION ALL\n" +
            "(SELECT id,name,NULL as name_en,'Enterprise' AS sign FROM enterprise WHERE name like concat('%',?1,'%') ORDER BY id) \n" +
            "UNION ALL\n" +
            "(SELECT id,name,NULL as name_en,'Product' AS sign FROM product WHERE name like concat('%',?1,'%') ORDER BY id)\n" +
            ")as a ORDER BY ?#{#pageable}",
            countQuery = "SELECT count(*) FROM (\n" +
                    "(SELECT id,name,latin_name as name_en,'Organism' AS sign  FROM organism WHERE name like concat('%',?1,'%') ORDER BY id) \n" +
                    "UNION ALL\n" +
                    "(SELECT id,name,latin_name as name_en,'Specimen' AS sign FROM specimen WHERE name like concat('%',?1,'%') ORDER BY id) \n" +
                    "UNION ALL\n" +
                    "(SELECT id,name,null as name_en,'Park' AS sign FROM park WHERE name like concat('%',?1,'%') ORDER BY id) \n" +
                    "UNION ALL\n" +
                    "(SELECT id,name,null as name_en,'Ecosystem' AS sign FROM ecosystem WHERE name like concat('%',?1,'%') ORDER BY id) \n" +
                    "UNION ALL\n" +
                    "(SELECT id,name,latin as name_en,'Conservation_0' AS sign FROM animal_conservation WHERE name like concat('%',?1,'%') ORDER BY id) \n" +
                    "UNION ALL\n" +
                    "(SELECT id,name,latin as name_en,'Conservation_1' AS sign FROM plant_conservation WHERE name like concat('%',?1,'%') ORDER BY id) \n" +
                    "UNION ALL\n" +
                    "(SELECT id,name,latin as name_en,'Conservation_2' AS sign FROM endangered_species WHERE name like concat('%',?1,'%') ORDER BY id) \n" +
                    "UNION ALL\n" +
                    "(SELECT id,name,NULL as name_en,'Vegetation' AS sign FROM vegetation WHERE name like concat('%',?1,'%') ORDER BY id) \n" +
                    "UNION ALL\n" +
                    "(SELECT id,name,NULL as name_en,'Enterprise' AS sign FROM enterprise WHERE name like concat('%',?1,'%') ORDER BY id) \n" +
                    "UNION ALL\n" +
                    "(SELECT id,name,NULL as name_en,'Product' AS sign FROM product WHERE name like concat('%',?1,'%') ORDER BY id))as a",nativeQuery = true)
    public List<Object> findAllByKeyWords(String key,Pageable pageable);

    @Query(value = "SELECT count(*) FROM (\n" +
            "(SELECT id,name,latin_name as name_en,'Organism' AS sign  FROM organism WHERE name like concat('%',?1,'%') ORDER BY id) \n" +
            "UNION ALL\n" +
            "(SELECT id,name,latin_name as name_en,'Specimen' AS sign FROM specimen WHERE name like concat('%',?1,'%') ORDER BY id) \n" +
            "UNION ALL\n" +
            "(SELECT id,name,null as name_en,'Park' AS sign FROM park WHERE name like concat('%',?1,'%') ORDER BY id) \n" +
            "UNION ALL\n" +
            "(SELECT id,name,null as name_en,'Ecosystem' AS sign FROM ecosystem WHERE name like concat('%',?1,'%') ORDER BY id) \n" +
            "UNION ALL\n" +
            "(SELECT id,name,latin as name_en,'Conservation_0' AS sign FROM animal_conservation WHERE name like concat('%',?1,'%') ORDER BY id) \n" +
            "UNION ALL\n" +
            "(SELECT id,name,latin as name_en,'Conservation_1' AS sign FROM plant_conservation WHERE name like concat('%',?1,'%') ORDER BY id) \n" +
            "UNION ALL\n" +
            "(SELECT id,name,latin as name_en,'Conservation_2' AS sign FROM endangered_species WHERE name like concat('%',?1,'%') ORDER BY id) \n" +
            "UNION ALL\n" +
            "(SELECT id,name,NULL as name_en,'Vegetation' AS sign FROM vegetation WHERE name like concat('%',?1,'%') ORDER BY id) \n" +
            "UNION ALL\n" +
            "(SELECT id,name,NULL as name_en,'Enterprise' AS sign FROM enterprise WHERE name like concat('%',?1,'%') ORDER BY id) \n" +
            "UNION ALL\n" +
            "(SELECT id,name,NULL as name_en,'Product' AS sign FROM product WHERE name like concat('%',?1,'%') ORDER BY id))as a",nativeQuery = true)
    public Long getCountByKey(String key);

    @Query(value = "select id,name,latin_name,alias,habitat,use_values from organism where if(?1 !='',name like concat('%',?1,'%'),1=1) AND if(?2 !='',latin_name like concat('%',?2,'%'),1=1) and if(?3 !='',id in(?4),1=1) ORDER BY ?#{#pageable}",
            countQuery = "select count(*) from organism where if(?1 !='',name like concat('%',?1,'%'),1=1) AND if(?2 !='',latin_name like concat('%',?2,'%'),1=1) and if(?3 !='',id in(?4),1=1)",nativeQuery = true)
    List<Object> queryAllByKeyAndIds(String name,String latin_name,String sign,List<Long> ids, Pageable pageable);

    @Query(value = "select count(*) from organism where if(?1 !='',name like concat('%',?1,'%'),1=1) AND if(?2 !='',latin_name like concat('%',?2,'%'),1=1) and if(?3 !='',id in(?4),1=1)",nativeQuery = true)
    Long queryCountByKey(String name,String latin_name,String sign,List<Long> ids);

    @Query(value = "select * from organism",nativeQuery = true)
    List<Object> getCount();

    /**
     * 返回贵州省下维管、节肢、脊索动物的数量
     * @param phylumn
     *
     * @return
     */
    @Query(value = "select count(1) from taxonomy WHERE phylumn LIKE concat('%',?1,'%') and organism_id in(?2) ",nativeQuery = true)
    Long getCountByPhulumn(String phylumn,List<Long> areaIds);

    @Query(value = "select count(1) from organism WHERE use_values LIKE concat('%',?1,'%') and id in(?2)  ",nativeQuery = true)
    Long getCountByUseValue(String userValue,List<Long> areaIds);

    @Query(value = "select count(1) from organism where id in(?1) AND id in(?2)",nativeQuery = true)
    Long getCountByFamilyAndGenus(List<Long> ids,List<Long> ids2);

    @Query(value = "select id,name,latin_name from organism where id in(?1) AND id in(?2) ORDER BY ?#{#pageable}",
            countQuery = "select count(*) from organism where id in(?1) AND id in(?2)",nativeQuery = true)
    List<Object> getOrganismInfoByFamilyAndGenus(List<Long> ids,List<Long> ids1,Pageable pageable);

    @Query(value = "select organism_id from taxonomy where if(?1 !='',kingdom = ?1,1=1) AND if(?2 !='',phylumn like concat('%',?2,'%'),1=1) AND if(?3 !='',t_class like concat('%',?3,'%'),1=1) AND if(?4 !='',t_order like concat('%',?4,'%'),1=1) AND if(?5 !='',family like concat('%',?5,'%'),1=1) AND if(?6 !='',genus like concat('%',?6,'%'),1=1) group by organism_id",nativeQuery = true)
    List<Long> getOrganismIdByFamilyAndGenus(String kingdom,String phylumn,String tClass,String tOrder,String family,String genus);

}
